package com.cyx.gametool.controller;

import cn.hutool.core.io.file.FileNameUtil;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.log.StaticLog;
import com.cyx.gametool.base.CallBack;
import com.cyx.gametool.cons.Result;
import com.cyx.gametool.cons.SQLCons;
import com.cyx.gametool.db.DBConfig;
import com.cyx.gametool.db.HikariDBPool;
import com.cyx.gametool.excel.ExcelColumnTitle;
import com.cyx.gametool.excel.ExcelTableData;
import com.cyx.gametool.mgr.ControllerMgr;
import javafx.application.Platform;
import javafx.beans.value.ChangeListener;
import javafx.beans.value.ObservableValue;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.Node;
import javafx.scene.control.*;
import javafx.scene.image.Image;
import javafx.scene.image.ImageView;
import javafx.scene.layout.AnchorPane;

import javax.sql.DataSource;
import java.net.URL;
import java.sql.Connection;
import java.util.*;

public class ExcelToMysqlController extends AbsBaseController implements Initializable {

    @FXML
    public RadioButton radioButtonConfig1;

    @FXML
    public RadioButton radioButtonConfig2;

    @FXML
    public RadioButton radioButtonConfig3;

    @FXML
    public ToggleGroup sqlConfig;

    @Override
    public void initialize(URL url, ResourceBundle resourceBundle) {
        Toggle selectedToggle = sqlConfig.getSelectedToggle();
        // 选择mysql配置的单选框分组
        sqlConfig.selectedToggleProperty().addListener(new ChangeListener<Toggle>() {
            @Override
            public void changed(ObservableValue<? extends Toggle> observable, Toggle oldValue, Toggle newValue) {
                RadioButton oldRadioButton = (RadioButton) oldValue;
                if (oldRadioButton != null) {
                    // 旧的单选框取消选择的背景颜色css样式
                    oldRadioButton.getParent().getStyleClass().remove("vbox-select-style");
                }

                RadioButton newRadioButton = (RadioButton) newValue;
                if (newRadioButton != null) {
                    // 设置选择配置的背景颜色css样式
                    newRadioButton.getParent().getStyleClass().add("vbox-select-style");
                }
                System.out.println("谁被选择::    " + newRadioButton.getText());
            }
        });

    }

    /**
     * 获取某个控件所在的数据库配置
     */
    private DBConfig getDbConfigByNode(Node chooseNode) {
        TextField[] mysqlConfig = new TextField[5];
        chooseNode.getParent().getChildrenUnmodifiable().stream().filter(node -> node instanceof AnchorPane).forEach(node -> {
            AnchorPane anchorPane = (AnchorPane) node;
            ObservableList<Node> childrenUnmodifiable = anchorPane.getChildrenUnmodifiable();
            int index = 0;
            for (Node child : childrenUnmodifiable) {
                if (child instanceof TextField textField) {
                    mysqlConfig[index++] = textField;
                }
            }
        });
        if (checkConfig(mysqlConfig)) {
            return new DBConfig(mysqlConfig[0].getText(), Integer.parseInt(mysqlConfig[1].getText()), mysqlConfig[2].getText(), mysqlConfig[3].getText(), mysqlConfig[4].getText());
        }
        return null;
    }

    private DBConfig getDbConfigByToggleGroup() {
        Toggle selectedToggle = sqlConfig.getSelectedToggle();
        if (selectedToggle == null) {
            return null;
        }
        RadioButton radioButton = (RadioButton) selectedToggle;
        return getDbConfigByNode(radioButton);
    }

    /**
     * 测试连接按钮事件
     *
     * @param actionEvent
     */
    @FXML
    public void testConnect(ActionEvent actionEvent) {

        Button button = (Button) actionEvent.getSource();
        // host、port、username、password、database
        DBConfig dbConfig = getDbConfigByNode(button);
        // 检测配置是否正确
        if (dbConfig != null) {
            // 测试连接
            testConnect(dbConfig, button, result -> {
                // 使用Platform.runLater确保在JavaFX应用程序线程中更新UI
                Platform.runLater(() -> {
                    String imageUrl = "";
                    if (result.isSuccess()) {
                        imageUrl = "/static/png/mysql_success.png";
                    } else {
                        imageUrl = "/static/png/mysql_fail.png";
                    }
                    button.setGraphic(new ImageView(new Image(Objects.requireNonNull(getClass().getResourceAsStream(imageUrl)))));
                });
            });
        } else {
            button.setGraphic(null);
            // 配置不正确
            ControllerMgr.getInstance().getExcelConfigController().getMyPrintfLog().addErrorLog("数据库配置不正确");
        }

    }

    /**
     * 检查配置是否正确
     *
     * @param mysqlConfig
     * @return
     */
    private boolean checkConfig(TextField[] mysqlConfig) {
        for (TextField textField : mysqlConfig) {
            if (StrUtil.isEmpty(textField.getText())) {
                textField.getStyleClass().add("input-error-border");
                return false;
            } else {
                textField.getStyleClass().remove("input-error-border");
            }
        }
        return true;
    }

    private void testConnect(DBConfig dbConfig, Button button, CallBack callBack) {
        String dbUrl = "jdbc:mysql://" + dbConfig.getHost() + ":" + dbConfig.getPort() + "/" + dbConfig.getDatabase();
        button.setGraphic(new ImageView(new Image(Objects.requireNonNull(getClass().getResourceAsStream("/static/png/test.png")))));
        ThreadUtil.execAsync(() -> {
            try {
                DataSource ds = new SimpleDataSource(dbUrl, dbConfig.getUser(), dbConfig.getPassword());
                Connection connection = ds.getConnection();
                if (connection != null) {
                    callBack.onCallBack(Result.success());
                } else {
                    callBack.onCallBack(Result.fail());
                }
                ds.getConnection().close();
            } catch (Exception e) {
                callBack.onCallBack(Result.error());
            }

        });
    }


    /**
     * 转换excel数据到mysql数据库
     *
     * @param excelTableData
     */
    public void excelToMysqlSql(ExcelTableData excelTableData) {

        String sheetName = excelTableData.getSheetName();

        DBConfig dbConfigByToggleGroup = getDbConfigByToggleGroup();
        if (dbConfigByToggleGroup == null) {
            ControllerMgr.getInstance().getExcelConfigController().getMyPrintfLog().addErrorLog("没有指定数据库");
            return;
        }
        HikariDBPool hikariDBPool = null;
        try {
//            hikariDBPool = new HikariDBPool(dbConfigByToggleGroup);
            List<List<Object>> excelDataList = excelTableData.getExcelDataList();
            List<ExcelColumnTitle> excelColumnTitles = excelTableData.getExcelColumnTitles();
            if (excelColumnTitles == null || excelColumnTitles.isEmpty()) {
                return;
            }
            // 创建表sql
            StringBuilder createTableSql = new StringBuilder();
            // 插入数据sql
            StringBuilder insertSql1 = new StringBuilder(" (");
            StringBuilder insertSql2 = new StringBuilder("VALUES (");
            createTableSql.append(StrUtil.format(SQLCons.DROP_SQL, sheetName));
            createTableSql.append(StrUtil.format(SQLCons.CREATE_SQL1, sheetName));
            Set<Integer> primaryColumnIndexSet = new HashSet<>();
            // 主键 - 默认第一个可用字段
            String primaryColumnName = excelColumnTitles.get(0).getFieldName();
            for (ExcelColumnTitle excelColumnTitle : excelColumnTitles) {
                int columnIndex = excelColumnTitle.getColumnIndex();
                primaryColumnIndexSet.add(columnIndex);
                insertSql1.append(excelColumnTitle.getFieldName()).append(",");
                insertSql2.append("?,");
                if (excelColumnTitle.isNull()) {
                    createTableSql.append(StrUtil.format(SQLCons.CREATE_SQL2_1, excelColumnTitle.getFieldName(), excelColumnTitle.getFieldType().getMysqlType(), excelColumnTitle.getFieldDesc()));
                } else {
                    createTableSql.append(StrUtil.format(SQLCons.CREATE_SQL2, excelColumnTitle.getFieldName(), excelColumnTitle.getFieldType().getMysqlType(), excelColumnTitle.getFieldDesc()));
                }
            }
            createTableSql.append(StrUtil.format(SQLCons.CREATE_SQL3, primaryColumnName)).append(StrUtil.format(SQLCons.CREATE_SQL4, FileNameUtil.getPrefix(excelTableData.getSourceFile())));
            insertSql1.deleteCharAt(insertSql1.length() - 1).append(")");
            insertSql2.deleteCharAt(insertSql2.length() - 1).append(");");

            StaticLog.info(createTableSql.toString());
//        SqlExecutor.execute(hikariDBPool.getConnection(), SQLCons.CREATE_SQL3, sheetName);

            List<Object[]> aa = new ArrayList<>();
            for (int startIndex = excelTableData.getStartRowIndex() - 1; startIndex < excelDataList.size(); startIndex++) {
                List<Object> objects = excelDataList.get(startIndex);
                List<Object> paramList = new ArrayList<>();
                for (int i = 0; i < objects.size(); i++) {
                    if (primaryColumnIndexSet.contains(i)) {
                        paramList.add(objects.get(i));
                    }
                }
                aa.add(paramList.toArray());
            }
            StaticLog.info(StrUtil.format(SQLCons.INSERT_SQL1, sheetName) + insertSql1.toString() + insertSql2.toString());
//        SqlExecutor.executeBatch(hikariDBPool.getConnection(), StrUtil.format(SQLCons.INSERT_SQL1, sheetName) + insertSql1.toString() + insertSql2.toString(), aa);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (hikariDBPool != null) {
                hikariDBPool.close();
            }
        }
    }
}
